##
## Mysql.py 
##
## Offers mysql functionality for cwsAdmin script.
## (ie create database, create user)
##
## Author: Shannon Eric Peevey <speeves@erikin.com>, 17 Jan 2004
##

import os
import MySQLdb

class MysqlFunc:
    """
    Offers mysql functionality for cwsAdmin script.
    (ie create database, create user)

    Important Notes from the MySQL Docs:
    http://dev.mysql.com/doc/refman/5.0/en/grant.html
    1. GRANT supports hostnames up to 60 characters long.
    2. Database, table, column, and routine names can be up to 64 characters.
    3. Usernames can be up to 16 characters.
    
    """

    def AddMysqlDatabase(self, config, utilityObject):
        """ Add a database to the mysql database """
        ## DEBUGGING
        if config['General']["debugLevel"] == '10':
            import pdb; pdb.set_trace()
            
        # Create the connection to the database server
        conn = self.Connect(config)

        # Get the database name for one or databases to create
        print "\nNext, we need the name of the Database(s) that we would like to add."
        print "You can enter as many Databases as you would like."
        print ""
        print "Press 'q' to finish adding Databases\n"

        # set text for prompt
        databaseName = "Database Name? (q => finish) [q] "
        # grab single/multiple groupNames
        databaseNameList = utilityObject.GetObjectId(config, databaseName)
                                               
        # loop through the databaseNameList to create the database(s)
        for database in databaseNameList:
            ## DEBUGGING
            if config['General']["debugLevel"] == '5':
                print "database == ", database
            if database == 'q':
                break

            userExists = 'y'
            while userExists == 'y':
                # generate a random username
                newUser = self.generateUsername(config, utilityObject)

                # check to see if the user already exists
                # which means that we don't need to set passwords...
                # returns y/n
                userExists = self.MysqlCheckUserExists(config, newUser)

            totalLength = len(newUser) + len(config['Mysql']['Server' + config['Session']['Mysql']['CurrentServer']]["mysqlWebAppUserSuffix"])
            subtractLength =  totalLength - int(config['Mysql']['Server' + config['Session']['Mysql']['CurrentServer']]["mysqlWebAppUserNameLength"])
            if subtractLength > 0:
                # database = database[:-subtractLength]
                admUser = newUser[:-subtractLength] + config['Mysql']['Server' + config['Session']['Mysql']['CurrentServer']]["mysqlWebAppUserSuffix"]
            else:
                admUser = newUser + config['Mysql']['Server' + config['Session']['Mysql']['CurrentServer']]["mysqlWebAppUserSuffix"]

            ## DEBUGGING
            if config['General']["debugLevel"] == '5':
                print "database (after subtractLength) == ", database
                print "totalLength == " + str(totalLength)
                print "subtractLength == " + str(subtractLength)
                
            # create the cursor by which we will create the new databases
            cursor = conn.cursor()

            try:
                # set variables to create database, etc
                cursor.execute("CREATE DATABASE " + database + ";")

                # generate password for admin user
                password = utilityObject.GeneratePassword(config)

                # create user:password in password file
                # this user should be unique...
                for host in config['Mysql']['Server' + config['Session']['Mysql']['CurrentServer']]['mysqlAccessFQDN']:
                    ## DEBUGGING
                    if config['General']["debugLevel"] == '5':
                        import pdb; pdb.set_trace()

                    ## DEBUGGING
                    if config['General']["debugLevel"] == '5':
                        print "host == ", host
                        print "types of variables"
                        print "type(password) == ", type(password)
                        print "type(database) == ", type(database)
                        print "type(host) == ", type(host)
                        print "type(config['Mysql']['Server' + config['Session']['Mysql']['CurrentServer']][mysqlWebAppUserSuffix]) == ", type(config['Mysql']["mysqlWebAppUserSuffix"])

                    createWebAppUser = "GRANT USAGE ON *.* TO '" + admUser + "'@'" + host + "' IDENTIFIED BY '" + password[:10] + "';"
                    webAppUserRights = "GRANT " + config['Mysql']['Server' + config['Session']['Mysql']['CurrentServer']]["mysqlWebAppUserRights"] + " ON " + database + ".* TO '" + admUser + "'@'" + host + "' IDENTIFIED BY '" + password[:10] + "';"
                    if config['General']["debugLevel"] == '1':
                        print "createWebAppUser == ", createWebAppUser
                        print "webAppUserRights == ", webAppUserRights

                    try:
                        cursor.execute(createWebAppUser)
                        cursor.execute(webAppUserRights)
                        print "\nCreating a new Database and default user..."
                        print "The default admin username is ", admUser
                        print "The password for the default Web Application User is ", password[:10], "\n"
                    except MySQLdb.Error, e:
                        print "Error %d: %s" % (e.args[0], e.args[1])
            except MySQLdb.Error, e:
                print "Error %d: %s" % (e.args[0], e.args[1])

            # close the cursor to prepare for the next database
            cursor.close()

        conn.close()

    def DeleteMysqlDatabase(self, config, utilityObject):
        """ Add a database to the mysql database """
        ## DEBUGGING
        if config['General']["debugLevel"] == '5':
            import pdb; pdb.set_trace()
        # Create the connection to the database server
        conn = self.Connect(config)

        # Get the database name for one or databases to create
        print "\nNext, we need the name of the Database(s) that we would like to drop."
        print "You can enter as many Databases as you would like."
        print ""
        print "Press 'q' to finish adding Databases\n"

        # set text for prompt
        databaseName = "Database Name? (h => help, a => Show All, q => Finish) [q] "
        help = """
        Help Section
        -----------------------------------------------
        a => view a list of all databases
        f => finish entering databases and run the request
        h => view this page
        
        You may search for a single database: 
        Database Name? (a => Show All, q => Finish, h => help) testdatabase 
        This query only drops a database with an exact match 'testdatabase'.

        The wildcard '%' does not work with Drop Database
        -----------------------------------------------
        """
        # grab single/multiple groupNames
        databaseNameList = utilityObject.GetObjectId(config, databaseName, help)

        # loop through the databaseNameList to delete the database(s)
        for database in databaseNameList:
            if database == '%':
                self.SimpleShowAllDatabases(config)
            else:
                # create the cursor by which we will delete the database
                dbCursor = conn.cursor()

                ## DEBUGGING
                if config['General']["debugLevel"] == '1':
                    print "DROP DATABASE " + database + ";"
                # drop database
                dropDb = "DROP DATABASE " + database + ";"
                try:
                    dbCursor.execute(dropDb)
                    dbCursor.close()
                    # end of dbCursor


                    # Now we find all users with rights to the database, and we drop them
                    userCursor = conn.cursor()

                    # grab the users from mysql.db
                    selectDbPrivileges = "SELECT * FROM db WHERE Db = '" + database + "';"

                    try:
                        userCursor.execute(selectDbPrivileges)

                        # place the resultset of userCursor into userList (sequence)
                        userList = userCursor.fetchall()
                        # drop userCursor
                        userCursor.close()
                        # end of userCursor

                        # call RevokeUserRights to revoke rights and delete user if necessary
                        # Loop through userList and revoke the rights from the deleted database.
                        # Then: check to see if that user has any rights to anything else, and
                        # drop them if they don't.
                        for userRow in userList:
                            # set user and host
                            user = userRow[2]
                            host = userRow[0]
                            self.RevokeUserRights(config, conn, database, user, host)
                    except MySQLdb.Error, e:
                        print "Error %d: %s" % (e.args[0], e.args[1])
                except MySQLdb.Error, e:
                    print "Error %d: %s" % (e.args[0], e.args[1])

        print "Deleting a new Database and default user..."
        # close mysql connection
        conn.close()

                    
    def AddMysqlUser(self, config, utilityObject):
        """ Add a user to a mysql database """
 	""" Get the username for the new user """
        ## add by database or by user?
        userOrDb = raw_input("Add by User or Database? (u => user, d => database) [u] ")
        if userOrDb == "":
            userOrDb = "u"

        # create the connection to the database
        conn = self.Connect(config)

        # if userOrDb == 'u', get a list of users to add
        if userOrDb == 'u':
            self.AddMysqlUserToDatabases(config, utilityObject, conn)
        elif userOrDb == 'd':
            self.AddMysqlDatabaseToUsers(config, utilityObject, conn)
        else:
            print "Invalid command.  Please re-enter your choice."

        conn.close()

        # this is old non useful text...
        print "Adding user to the MySQL database..."
        print "Just a second while I verify..."
        

    def AddMysqlUserToDatabases(self, config, utilityObject, conn):
        """ This adds User(s) to multiple databases """
        ## DEBUGGING
        if config['General']["debugLevel"] == '10':
            import pdb; pdb.set_trace()
        # Get the database name for one or databases to create
        print "\nNext, we need the name of the User(s) for which we would like to add"
        print "database rights. You may enter as many Users as you would like."
        print ""
        print "Press 'q' to finish adding Users\n"

        # set text for prompt
        userName = "User Name? (q => finish) [q] "
        # grab single/multiple Names
        nameList = utilityObject.GetObjectId(config, userName)

        databaseList = []
        # for every item in nameList (user or db) add a list of users or databases
        for name in nameList:
            # check to see if the user already exists
            # which means that we don't need to set passwords...
            # returns y/n
            userExists = self.MysqlCheckUserExists(config, name)
            
            if len(databaseList) == 0:
                # Get the database name for one or databases to create
                print "\nNext, we need the name of the Databases(s) for which we would like to add"
                print "our list of User(s). You may enter as many Databases as you would like."
                print ""
                print "Press 'q' to finish adding Databases\n"

                # set text for prompt
                databaseName = "Database Name? (q => finish) [q] "
                # grab single/multiple Names
                databaseList = utilityObject.GetObjectId(config, databaseName)

            print "\nInformation for User " + name + "\n"
            if userExists == 'n':
                # generate the password for user
                password = self.generatePasswd(config, utilityObject)

            for db in databaseList:
                # loop through the database names and add all the user to every database
                # create cursor to add users
                cursor = conn.cursor()

                print "Access information for " + name + " on Database " + db + "\n"
                host = self.defineConnectionHost(config)
            
                # create user:password in password file
                if userExists == 'n':
                    createUser = "GRANT USAGE ON *.* TO '" + name + "'@'" + host + "' IDENTIFIED BY '" + password[:10] + "';"
                    userRights = "GRANT ALL PRIVILEGES ON " + db + ".* TO '" + name + "'@'" + host + "' IDENTIFIED BY '" + password[:10] + "';"
                else:
                    createUser = "GRANT USAGE ON *.* TO '" + name + "'@'" + host + "';"
                    userRights = "GRANT ALL PRIVILEGES ON " + db + ".* TO '" + name + "'@'" + host + "';"

                if config['General']["debugLevel"] == '5':
                    print "AddMysqlUserToDatabases.createUser == ", createUser
                    print "AddMysqlUserToDatabases.userRights == ", userRights
                try:
                    cursor.execute(createUser)
                    cursor.execute(userRights)
                except MySQLdb.Error, e:
                    print "Error %d: %s" % (e.args[0], e.args[1])
                    
                cursor.close()

            if config['Mysql']['Server' + config['Session']['Mysql']['CurrentServer']]["useMysqlUserIndex"] == '1':
                self.CheckMysqlNewuserTable(config, name)

    def AddMysqlDatabaseToUsers(self, config, utilityObject, conn):
        """ This adds User(s) to multiple databases """
        ## DEBUGGING
        if config['General']["debugLevel"] == '10':
            import pdb; pdb.set_trace()
        # Get the database name for one or databases to create
        print "\nNext, we need the name of the Databases(s) for which we would like to add"
        print "our User(s). You may enter as many Databases as you would like."
        print ""
        print "Press 'q' to finish adding Databases\n"
        
        # set text for prompt
        databaseName = "Database Name? (q => finish) [q] "
        # grab single/multiple Names
        databaseList = utilityObject.GetObjectId(config, databaseName)

        userList = []
        # for every item in nameList (user or db) add a list of users or databases
        for db in databaseList:
            if len(userList) == 0:
                # Get the database name for one or databases to create
                print "\nEnter User(s) which you would like to add to the " + db
                print "database. You may enter as many Users as you would like."
                print ""
                print "Press 'q' to finish adding Users\n"

                # set text for prompt
                userName = "User Name? (q => finish) [q] "
                # grab single/multiple Names
                userList = utilityObject.GetObjectId(config, userName)

            print "\nAccess information for " + db + "\n"  
            # get host from which user may connect to the database
            host = self.defineConnectionHost(config)

            # loop through userList and add rights to db
            for user in userList:
                # check to see if the user already exists
                # which means that we don't need to set passwords...
                # returns y/n
                userExists = self.MysqlCheckUserExists(config, user)
                
                # create cursor to add users
                cursor = conn.cursor()

                print "\nInformation for User " + user + "\n"
                if userExists == 'n':
                    # generate the password for user
                    password = self.generatePasswd(config, utilityObject)

                # create user:password in password file
                if userExists == 'n':
                    createUser = "GRANT USAGE ON *.* TO '" + user + "'@'" + host + "' IDENTIFIED BY '" + password[:10] + "';"
                    userRights = "GRANT ALL PRIVILEGES ON " + db + ".* TO '" + user + "'@'" + host + "' IDENTIFIED BY '" + password[:10] + "';"
                else:
                    createUser = "GRANT USAGE ON *.* TO '" + user + "'@'" + host + "';"
                    userRights = "GRANT ALL PRIVILEGES ON " + db + ".* TO '" + user + "'@'" + host + "';"

                if config['General']["debugLevel"] == '5':
                    print "AddMysqlDatabaseToUsers.createUser == ", createUser
                    print "AddMysqlDatabaseToUsers.userRights == ", userRights
                
                cursor.execute(createUser)
                cursor.execute(userRights)
                cursor.close()

                if config['Mysql']['Server' + config['Session']['Mysql']['CurrentServer']]["useMysqlUserIndex"] == '1':
                    self.CheckMysqlNewuserTable(config, user)



    def RemoveMysqlUser(self, config, utilityObject):
        """ Remove a user from a mysql database """
 	""" Get the username for the new user """
        ## add by database or by user?
        userOrDb = raw_input("Remove by User or Database? (u => user, d => database) [u] ")
        if userOrDb == "":
            userOrDb = "u"

        # create the connection to the database
        conn = self.Connect(config)

        # if userOrDb == 'u', get a list of users to add
        if userOrDb == 'u':
            self.RemoveMysqlUserFromDatabases(config, utilityObject, conn)
        elif userOrDb == 'd':
            self.RemoveMysqlDatabaseFromUsers(config, utilityObject, conn)
        else:
            print "Invalid command.  Please re-enter your choice."

        conn.close()


    def RemoveMysqlUserFromDatabases(self, config, utilityObject, conn):
        """ This removes User(s) from multiple databases """
        ## DEBUGGING
        if config['General']["debugLevel"] == '5':
            import pdb; pdb.set_trace()
        # Get the database name for one or databases to create
        print "\nNext, we need the name of the User(s) for which we would like to remove"
        print "database rights. You may enter as many Users as you would like."
        print ""
        print "Press 'q' to finish adding Users\n"

        # set text for prompt
        userName = "User Name? (q => finish) [q] "
        # grab single/multiple Names
        userList = utilityObject.GetObjectId(config, userName)

        specificUserList = []
        userRowCount = 0
        # for every item in nameList (user or db) add a list of users or databases
        for user in userList:
            # get a list of databases that user has rights to
            userCursor = conn.cursor()
            userCursorQuery = "SELECT DISTINCT * FROM db WHERE User like '" + user + "';"
            try:
                userCursor.execute(userCursorQuery)
                userListQueryResult = userCursor.fetchall()

                if len(userListQueryResult) > 1:
                    print "\nEnter the host here...  (add more descriptive text)"
                    for userRow in userListQueryResult:
                        print "(" + str(userRowCount) + ") User = " + userRow[2] + " Database = " + userRow[1] + " Host = " + userRow[0]
                        userRowCount = userRowCount + 1

                    # set text for prompt
                    specificUserName = "\nUser Name? (a => all, f => finish) [f] "
                    # grab single/multiple db names
                    specificUserListNumber = utilityObject.GetObjectId(config, specificUserName)
                    # loop through userListNumber to set the userList to hostnames
                    # instead of number entries
                    ## DEBUGGING
                    if config['General']["debugLevel"] == '5':
                        import pdb; pdb.set_trace()
                    for userEntryNumber in specificUserListNumber:
                        # userEntryNumber is a number corresponding to the index
                        # of userListQueryResult.  ie
                        #
                        # userEntryNumber == 1
                        # userListQueryResult[1]
                        #
                        ## DEBUGGING
                        if config['General']["debugLevel"] == '1':
                            try:
                                print "user == ", userListQueryResult[int(userEntryNumber)][2]
                                print "database == ", userListQueryResult[int(userEntryNumber)][1]
                                print "host == ", userListQueryResult[int(userEntryNumber)][0]
                            except:
                                # means that we want to delete all...
                                print "We want to delete all users..."
                                print "user == ", user
                                print "database == %"
                                print "host == %"
                        try:
                            user = userListQueryResult[int(userEntryNumber)][2]
                            database = userListQueryResult[int(userEntryNumber)][1]
                            host = userListQueryResult[int(userEntryNumber)][0]
                            self.RevokeUserRights(config, conn, database, user, host)
                        except:
                            user = user
                            database = '%'
                            host = '%'
                            self.RevokeUserRights(config, conn, database, user, host)
                else:
                    ## DEBUGGING
                    if config['General']["debugLevel"] == '1':
                        print "user == ", userListQueryResult[0][2]
                        print "database == ", userListQueryResult[0][1]
                        print "host == ", userListQueryResult[0][0]
                    user = userListQueryResult[0][2]
                    database = userListQueryResult[0][1]
                    host = userListQueryResult[0][0]
                    self.RevokeUserRights(config, conn, database, user, host)
            except MySQLdb.Error, e:
                print "Error %d: %s" % (e.args[0], e.args[1])

    def RemoveMysqlDatabaseFromUsers(self, config, utilityObject, conn):
        """ This removes User(s) from multiple databases """
        ## DEBUGGING
        if config['General']["debugLevel"] == '1':
            import pdb; pdb.set_trace()
        # Get the database name for one or databases to create
        print "\nNext, we need the name of the Databases(s) from which we would like to remove"
        print "our User(s). You may enter as many Databases as you would like."
        print ""
        print "Press 'q' to finish adding Databases\n"
        
        # set text for prompt
        databaseName = "Database Name? (q => finish) [q] "
        # grab single/multiple Names
        nameList = utilityObject.GetObjectId(config, databaseName)

        userList = []
        # for every item in nameList (user or db) add a list of users or databases
        for name in nameList:
            if len(userList) == 0:
                # Get the database name for one or databases to create
                print "\nEnter User(s) which you would like to remove from the " + name
                print "database. You may enter as many Users as you would like."
                print ""
                print "Press 'q' to finish adding Users\n"

                # set text for prompt
                userName = "User Name? (q => finish) [q] "
                # grab single/multiple Names
                userList = utilityObject.GetObjectId(config, userName)


            # loop through the user names and remove the users
            for user in userList:
                self.RevokeUserRights(config, conn, database, user, host)


    def RevokeUserRights(self, config, conn, database, user, host):
        """ Revoke the user's rights from a database, then check to see if they
        have any other rights to databases.  If not, then we delete the user completely
        from the system """
        # create cursor to revoke the rights from the user
        revokePrivilegesCursor = conn.cursor()
        revokePrivilegesQuery = "REVOKE ALL ON " + database + ".* FROM '" + user + "'@'" + host + "';"
        try:
            revokePrivilegesCursor.execute(revokePrivilegesQuery)

            # create cursor to select all users from mysql.db where user = 'user'
            selectDropUserCursor = conn.cursor()

            # grab the users from mysql.db WHERE User = 'user'
            selectDropUserCursorQuery = "SELECT * FROM db WHERE User = '" + user + "';"

            try:
                selectDropUserCursor.execute(selectDropUserCursorQuery)

                # Place the resultset into dropUserList
                dropUserList = selectDropUserCursor.fetchall()
                selectDropUserCursor.close()
                # end of selectDropUserCursor

                if len(dropUserList) == 0:
                    # we need to drop this user completely
                    # create fifth cursor to drop user
                    dropUserCursor = conn.cursor()
                    # drop user because they don't have rights to any other databases
                    # Deleting 'test1jU2'@'web2.erikin.com' ...
                    # DELETE FROM `user` WHERE User = 'test1jU2' AND Host = 'web2.erikin.com';
                    # DELETE FROM `db` WHERE User = 'test1jU2' AND Host = 'web2.erikin.com';
                    # DELETE FROM `tables_priv` WHERE User = 'test1jU2' AND Host = 'web2.erikin.com';
                    # DELETE FROM `columns_priv` WHERE User = 'test1jU2' AND Host = 'web2.erikin.com';
                    # Reloading the privileges ...
                    # FLUSH PRIVILEGES ;

                    dropUserFromUser = "DELETE FROM user WHERE User like '" + user + "' AND Host like '" + host + "';"
                    dropUserFromDb = "DELETE FROM db WHERE User like '" + user + "' AND Host like '" + host + "';"
                    dropUserFromTables_Priv = "DELETE FROM tables_priv WHERE User like '" + user + "' AND Host like '" + host + "';"
                    dropUserFromColumns_Priv = "DELETE FROM columns_priv WHERE User like '" + user + "' AND Host like '" + host + "';"
                    flushPrivileges = "FLUSH PRIVILEGES ;"

                    ## DEBUGGING
                    if config['General']["debugLevel"] == '5':
                        print "dropUserFromUser == ", dropUserFromUser
                        print "dropUserFromDb == ", dropUserFromDb
                        print "dropUserFromTables_Priv == ", dropUserFromTables_Priv
                        print "dropUserFromColumns_Priv == ", dropUserFromColumns_Priv
                        print "flushPrivileges == ", flushPrivileges

                    try:
                        dropUserCursor.execute(dropUserFromUser)
                        dropUserCursor.execute(dropUserFromDb)
                        dropUserCursor.execute(dropUserFromTables_Priv)
                        dropUserCursor.execute(dropUserFromColumns_Priv)
                        dropUserCursor.execute(flushPrivileges)
                    except MySQLdb.Error, e:
                        print "Error %d: %s" % (e.args[0], e.args[1])

                    dropUserCursor.close()
                    # end of dropUserCursor

            except MySQLdb.Error, e:
                print "Error %d: %s" % (e.args[0], e.args[1])
        except MySQLdb.Error, e:
            print "Error %d: %s" % (e.args[0], e.args[1])

        revokePrivilegesCursor.close()
        # end of revoke privileges from user
                


    def CheckMysqlNewuserTable(self, config, user):
        """ check for the user in the newuser table
        if they are not there, add them into the table. This is used by the /newuser
        application to update the user password """
        conn = self.Connect(config)
        cursor = conn.cursor()
        selectNewuser = "SELECT * FROM " + config['Mysql']['Server' + config['Session']['Mysql']['CurrentServer']]["mysqlUserIndexTable"] + " WHERE " + config['Mysql']["mysqlUserIndexCol"] + " = '" + user + "';"
        if config['General']["debugLevel"] == '1':
            print "selectNewUser == ", selectNewuser

        try:
            cursor.execute(selectNewuser)
            numRows = int(cursor.rowcount)

            if (numRows == 0):
                insertNewuser = "INSERT INTO " + config['Mysql']['Server' + config['Session']['Mysql']['CurrentServer']]["mysqlUserIndexTable"] + " (" + config['Mysql']["mysqlUserIndexCol"] + ") VALUES ('" + user + "');"
                if config['General']["debugLevel"] == '1':
                    print "insertNewuser == ", insertNewuser
                try:
                    cursor.execute(insertNewuser)
                except MySQLdb.Error, e:
                    print "Error %d: %s" % (e.args[0], e.args[1])
        except MySQLdb.Error, e:
            print "Error %d: %s" % (e.args[0], e.args[1])
            
        cursor.close()
        conn.close()


    def ShowUserAccess(self, config, utilityObject):
        """ Add a user to a mysql database """
 	""" Get the username for the new user """
        ## add by database or by user?
        userOrDb = raw_input("Search by User or Database? (u => user, d => database) [u] ")
        if userOrDb == "":
            userOrDb = "u"

        # create the connection to the database
        conn = self.Connect(config)

        # if userOrDb == 'u', get a list of users to add
        if userOrDb == 'u':
            self.ShowUserAccessByUser(config, utilityObject, conn)
        elif userOrDb == 'd':
            self.ShowUserAccessByDatabase(config, utilityObject, conn)
        else:
            print "Invalid command.  Please re-enter your choice."

        conn.close()


    def ShowUserAccessByDatabase(self, config, utilityObject, conn):
        """ print the user access by database """
        # Get the database name
        print "\nPlease enter the name of the Database(s) for which you would like to search."
        print "You may enter as many database names as you would like."
        print ""
        print "Press 'q' to finish adding databases\n"

        # set text for prompt
        databaseName = "Database Name? (h => help, a => Show All, q => Finish) [q] "
        help = """
        Help Section
        -----------------------------------------------
        a => view a list of all databases
        f => finish entering databases and run the request
        h => view this page
        
        You may search for a single database: 
        Database Name? (a => Show All, q => Finish, h => help) testdatabase 
        This query only returns a database with an exact match 'testdatabase'.

        You may specify a wildcard, by placing a '%' anywhere in the database name: 
        Database Name? (h => help, a => Show All, q => Finish) test% 
        This returns any database which begins with 'test'.
        -----------------------------------------------
        """

        # grab single/multiple groupNames
        databaseNameList = utilityObject.GetObjectId(config, databaseName, help)

        for database in databaseNameList:
            cursor = conn.cursor()
            selectDb = "SELECT * FROM db WHERE db LIKE '" + database + "';"
            if config['General']["debugLevel"] == '5':
                print "selectDb == ", selectDb

            try:
                cursor.execute(selectDb)

                # get the resultset as a tuple
                result = cursor.fetchall()

                header1spacing = 20 - len("user")
                header2spacing = 20 - len("host")

                print "\n" + selectDb
                print "user" + " "*header1spacing + " | host" + " "*header2spacing + " | sel | ins | upd | del | cre | dro | gra | ref | ind | alt | tmp | lck \n"
                #iterate through the resultset
                # print the results in the following format
                # database | host | select | insert | update | delete | others
                for record in result:
                    column1spacing = 20 - len(record[2])
                    column2spacing = 20 - len(record[0])
                    column3spacing = 2

                    privString = ''
                    for priv in record[3:]:
                        privString = privString + "|" + " "*column3spacing + str(priv) + " "*column3spacing
                    #print str(record[1]) + " "*column1spacing + " | " + str(record[0]) + " "*column2spacing + " | " + str(record[3:])
                    print str(record[2]) + " "*column1spacing + " | " + str(record[0]) + " "*column2spacing + " " + privString

                print
            except MySQLdb.Error, e:
                print "Error %d: %s" % (e.args[0], e.args[1])
            


    def ShowUserAccessByUser(self, config, utilityObject, conn):
        """ print the databases to which a user has access """
        # Get the user name
        print "\nPlease enter the name of the User(s) for which you would like to search."
        print "You may enter as many User names as you would like."
        print ""
        print "Press 'q' to finish adding Users\n"

        # set text for prompt
        userName = "User Name? (h => help, a => Show All, q => Finish) [q] "
        help = """
        Help Section
        -----------------------------------------------
        a => view a list of all users
        f => finish entering users and run the request
        h => view this page
        
        You may search for a single user: 
        User Name? (a => Show All, q => Finish, h => help) testuser 
        This query only returns a user with an exact match 'testuser'.

        You may specify a wildcard, by placing a '%' anywhere in the user name: 
        User Name? (h => help, a => Show All, q => Finish) test% 
        This returns any user which begins with 'test'.
        -----------------------------------------------
        """

        # grab single/multiple groupNames
        userNameList = utilityObject.GetObjectId(config, userName, help)

        for user in userNameList:
            cursor = conn.cursor()
            selectDbUser = "SELECT * FROM db WHERE user LIKE '" + user + "';"
            if config['General']["debugLevel"] == '5':
                print "selectDbUser == ", selectDbUser

            try:
                cursor.execute(selectDbUser)

                # get the resultset as a tuple
                result = cursor.fetchall()

                header1spacing = 20 - len("database")
                header2spacing = 20 - len("host")

                print "\n" + selectDbUser
                print "database" + " "*header1spacing + " | host" + " "*header2spacing + " | sel | ins | upd | del | cre | dro | gra | ref | ind | alt | tmp | lck \n"
                #iterate through the resultset
                # print the results in the following format
                # database | host | select | insert | update | delete | others
                for record in result:
                    column1spacing = 20 - len(record[1])
                    column2spacing = 20 - len(record[0])
                    column3spacing = 2

                    privString = ''
                    for priv in record[3:]:
                        privString = privString + "|" + " "*column3spacing + str(priv) + " "*column3spacing
                    #print str(record[1]) + " "*column1spacing + " | " + str(record[0]) + " "*column2spacing + " | " + str(record[3:])
                    print str(record[1]) + " "*column1spacing + " | " + str(record[0]) + " "*column2spacing + " " + privString
                print 
            except MySQLdb.Error, e:
                print "Error %d: %s" % (e.args[0], e.args[1])
            
    def ShowDatabases(self, config, utilityObject):
        """ print the databases to which a user has access """
        conn = self.Connect(config)

        # Get the database name for one or databases to create
        print "\nPlease enter the name of the Database(s) for which you would like to search."
        print "You may enter as many Database names as you would like."
        print ""
        print "Press 'q' to finish adding Databases\n"

        # set text for prompt
        databaseName = "Database Name? (h => help, a => Show All, q => Finish) [q] "
        help = """
        Help Section
        -----------------------------------------------
        a => view a list of all databases
        f => finish entering databases and run the request
        h => view this page
        
        You may search for a single database: 
        Database Name? (a => Show All, q => Finish, h => help) testdatabase 
        This query only returns a database with an exact match 'testdatabase'.

        You may specify a wildcard, by placing a '%' anywhere in the database name: 
        Database Name? (h => help, a => Show All, q => Finish) test% 
        This returns any database which begins with 'test'.
        -----------------------------------------------
        """
        
        # grab single/multiple groupNames
        databaseNameList = utilityObject.GetObjectId(config, databaseName, help)

        # loop through our database names and return results
        for name in databaseNameList:
            ## DEBUGGING
            if config['General']["debugLevel"] == '5':
                import pdb; pdb.set_trace()
                
            cursor = conn.cursor()
            showDatabases = "SHOW DATABASES LIKE '" + name + "';"
            if config['General']["debugLevel"] == '5':
                print "showDatabases == ", showDatabases
            try:
                cursor.execute(showDatabases)

                # get the resultset as a tuple
                result = cursor.fetchall()

                # print the header for the database
                print "\n" + showDatabases
                #iterate through the resultset
                # print the results 
                for record in result:
                    print record[0]

                print
            except MySQLdb.Error, e:
                print "Error %d: %s" % (e.args[0], e.args[1])


    def SimpleShowAllDatabases(self, config):
        """ Simply returns a printout of all databases """
        conn = self.Connect(config)
        cursor = conn.cursor()
        showDatabases = "SHOW DATABASES LIKE '%';"
        if config['General']["debugLevel"] == '5':
            print "showDatabases == ", showDatabases
        try:
            cursor.execute(showDatabases)
            
            # get the resultset as a tuple
            result = cursor.fetchall()
        
            # print the header for the database
            print "\n" + showDatabases
            #iterate through the resultset
            # print the results 
            for record in result:
                print record[0]
            print
        except MySQLdb.Error, e:
            print "Error %d: %s" % (e.args[0], e.args[1])
            
        conn.close()


    def Connect(self, config, mysqlDB="mysql"):
        """ Connect to a Mysql database """
        try:
            if config['Mysql']['Server' + config['Session']['Mysql']['CurrentServer']]["mysqlSocket"] != "":
		db = MySQLdb.connect(config['Mysql']['Server' + config['Session']['Mysql']['CurrentServer']]["mysqlHostname"], config['Mysql']['Server' + config['Session']['Mysql']['CurrentServer']]["mysqlUser"], config['Mysql']['Server' + config['Session']['Mysql']['CurrentServer']]["mysqlUserPassword"], mysqlDB, unix_socket=config['Mysql']['Server' + config['Session']['Mysql']['CurrentServer']]["mysqlSocket"])
            else:
		db = MySQLdb.connect(config['Mysql']['Server' + config['Session']['Mysql']['CurrentServer']]["mysqlHostname"], config['Mysql']['Server' + config['Session']['Mysql']['CurrentServer']]["mysqlUser"], config['Mysql']['Server' + config['Session']['Mysql']['CurrentServer']]["mysqlUserPassword"], mysqlDB, port=config['Mysql']['Server' + config['Session']['Mysql']['CurrentServer']]["mysqlPort"])	
            return db
        except MySQLdb.Error, e:
            print "Error %d: %s" % (e.args[0], e.args[1])


    def mysqlUserIndex(self, user, mysqlDict):
        ####  We need to make this conditional on having a db of users on mysql ####
        # add the euid to the newuser.euid table
        conn = self.Connect(config['Mysql']['Server' + config['Session']['Mysql']['CurrentServer']]["mysqlHostname"], config['Mysql']["mysqlUser"], config['Mysql']["mysqlUserPassword"], config['Mysql']["mysqlUserIndexDb"], config['Mysql']["mysqlPort"], config['Mysql']["mysqlSocket"])

        cursor = conn.cursor()
                
        # set variables to create database, etc
        insertUser = "INSERT INTO " + config['Mysql']['Server' + config['Session']['Mysql']['CurrentServer']]["mysqlUserIndexTable"] + " (" + config['Mysql']["mysqlUserIndexCol"] + ") VALUES ('" + user + "');"
        if config['General']["debugLevel"] == '1':
            print insertUser

        try:
            cursor.execute(insertUser)
        except MySQLdb.Error, e:
            print "Error %d: %s" % (e.args[0], e.args[1])
            
        del mysql_instance

    def MysqlCheckUserExists(self, config, user):
        """ Check to see if the user already exists in the mysql.db table, (meaning that
            they already of a user, and don't need their password modified/created"""
        conn = self.Connect(config)
        cursor = conn.cursor()
        selectNewuser = "SELECT * FROM db WHERE User = '" + user + "';"
        if config['General']["debugLevel"] == '1':
            print "selectNewUser == ", selectNewuser
        try:
            cursor.execute(selectNewuser)
            numRows = int(cursor.rowcount)

            if numRows > 0:
                exists = 'y'
            else:
                exists = 'n'

            return exists

        except MySQLdb.Error, e:
            print "Error %d: %s" % (e.args[0], e.args[1])


    def generatePasswd(self, config, utilityObject):
        """create password for user
           autogenerate or manual input"""
        autogeneratepw = raw_input("Autogenerate Password for User? (y/n) [y] ")
        if autogeneratepw == "":
            autogeneratepw = 'y'
            
        if autogeneratepw == 'y':
            password = utilityObject.GeneratePassword(config)
        else:
            password = raw_input("User's Password? ")

        return password

    def generateUsername(self, config, utilityObject):
        """create username for user
           autogenerate or manual input"""
        autogenerateusername = raw_input("Autogenerate Username for User? (y/n) [y] ")
        if autogenerateusername == "":
            autogenerateusername = 'y'
            
        if autogenerateusername == 'y':
            username = utilityObject.GeneratePassword(config)
        else:
            username = raw_input("Username? ")

        return username


    def defineConnectionHost(self, config):
        """define which hosts that the user will be able to connect from"""
        connectHost = config['Mysql']['Server' + config['Session']['Mysql']['CurrentServer']]["defaultConnectHost"]
        print "This user will only be able to connect from " + connectHost
        print "(ie phpMyAdmin or Web Server)"
        correct = raw_input("Is this correct? (y/n) [y] ")
        if correct == "":
            correct = 'y'

        if correct == "y":
            host = connectHost
        else:
            host = raw_input("Additional Host Address? (IP or hostname) ")

        return host

